Thanks to those who attended my PowerPivot session at SharePoint Saturday in Manhattan yesterday. Click here to download my presentation. The download includes the slides, the spreadsheet example I used, and a text file containing all the DAX code. Feel free to email me at kgoff@kevinsgoff.net if you have any questions.
July 31, 2011
June 30, 2011
February 06, 2011
January 05, 2011
January 01, 2011
December 04, 2010
November 07, 2010
11-07-2010 - SetFocus BI/SQL Master's Program Tip: SSRS 2008/SSRS 2008 R2 OLAP reports and overwriting MDX parameters
In the SetFocus Business Intelligence Master's Program, I show many examples of building SSRS reports against Analysis Services (OLAP) databases. I've actually been showing this going back to SQL Server 2005.
SSRS 2008 (and SSRS 2008R2) have some tremendous reporting enhancements, but unfortunately introduced a feature that caught some people by surprise. The issue is a rather intricate one, and goes as follows:
Suppose you create a report against an OLAP cube. You start by creating a report dataset, using the OLAP query designer. You drag some columns from the cube metadata into the result set area, and you also define one of the OLAP dimensions as a parameter. SSRS creates a second dataset with MDX code that retrieves member values from the dimension attribute you defined as a parameter in the primary dataset. (This allows SSRS to display the parameter values in a report dropdown).
With me so far?
Now suppose you want to modify the MDX code for the 2nd dataset (which SSRS builds as a hidden dataset), to implement some custom behavior for the dropdown. For example, instead of showing all cities in a dropdown, you only want to show those cities with sales (or sales in the last 6 months, etc.) So you retrieve the MDX code for the 2nd (hidden) dataset, and you modify the MDX code. You run the report again, and the dropdown shows values the way you want to show them.
OK, so life is good…but this story isn't over.
Now go back to the original (primary) dataset and add a column/measure. (For example, maybe initially the report had 4 measures, and you have to add a fifth). So you add a measure to the primary dataset, you save it, you modify the report layout to add the new measure/column, and then you run the report…
And then you discover something – the dropdown parameter isn't showing the values in the dropdown according to the logic you implemented when you manually modified the MDX code. As a matter of fact, the dropdown is showing the available parameter values as if you'd never modified the MDX code at all!!!
Here's what happened – when we changed the primary dataset to add a new column, SSRS (well, specifically, Business Intelligence Development Studio) overwrote our custom MDX code with the original MDX code for the dimension parameter!!!
OUCH! This is actual behavior in SSRS 2008 and 2008 R2. As for a workaround, there's good news and bad news. The good news is that there's a workaround in the form of a setting. The bad news (well, more like "not great news") is that BIDS doesn't expose the setting – we need to modify the actual XML code in the RDL file.
So let's look at an actual example:
Suppose we create a simple OLAP report against AdventureWorks 2008. We want to show Internet Sales Amount by Product Category, and we want the user to select a country from the Customer Dimension/Country Attribute (Figure 1)
Figure 1:
After we save this dataset, SSRS creates a 2nd dataset that retrieves the countries from the Customer dimension. By default this 2nd dataset doesn't show in the DataSet window pane. However, we can right-click and show Hidden Datasets, which will wind up displaying the 2nd dataset (which SSRS called CustomerCountry, in Figure 2):
Figure 2:
If we right-click on this 2nd dataset and modify the query, SSRS loads the query designer with the generated MDX code (Figure 3). The code retrieves all countries in alphabetic order, plus the All Country (Customer) total.
Figure 3:
Now suppose we want to modify the query (and therefore the contents of the dropdown) to show the countries in order of sales for the country. So we modify the MDX query to use the MDX ORDER function, to show the countries in order based on sales, in Figure 4: (i.e. country with the highest sales will be first in the dropdown)
Figure 4:
Now we run the report (which shows Product Category and Internet Sales amount, and the prompt for Country with the correct sort order), in Figure 5:
Figure 5:
OK, now let's go back to the original main dataset, and add a new column (Internet Gross Profit), in Figure 6:
Figure 6:
We add the column to the designer as well, and then go run the report (Figure 7). Notice how the dropdown appears to have "lost" the behavior we implemented with our custom MDX code. This is the root of the behavior in SSRS 2008. What happened was that when we modified the dataset in the previous step, SSRS overwrote our custom MDX changes (without warning us!) To prevent this behavior, we need to modify the RDL file manually, to add a new setting (next step).
Figure 7:
We need to open the RDL file manually, which you can do by right-clicking on the report in Solution Explorer and taking the option "View Code". At the end of the XML content for the hidden dataset, add the following line: <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> (Figure 8).
Figure 8:
Now to test, go back and repeat step 4 (to put the custom MDX code back into the hidden dataset), and then pull up the main dataset and add another column, such as Internet Freight Cost (Figure 9). Previously, this would overwrite our custom MDX code. However, because of the SuppressAutoUpdate setting, SSRS will not update the MDX code.
Figure 9:
So if we go run the report after making the dataset change, our MDX code now stays!
Figure 10:
